package Dao;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Collection;
import java.util.Collections;
import java.util.Iterator;

import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.ResultSetExtractor;



import Bean.BeanBook;
import Bean.BeanOrderDetail;
import Bean.BeanRating;
import cart.ShoppingCart;
import cart.ShoppingCartItem;
import exception.BookNotFoundException;
import exception.BooksNotFoundException;
import exception.OrderException;

public class BookDao {
	ArrayList<BeanBook> books;
	private JdbcTemplate jdbcTemplate;

	public JdbcTemplate getJdbcTemplate() {
		return jdbcTemplate;
	}

	public void setJdbcTemplate(JdbcTemplate jdbcTemplate) {
		this.jdbcTemplate = jdbcTemplate;
	}
	public void addBook(BeanBook book) {
		String sql="insert into books(title,author,publisher,publishing_time,book_price,category_id,inventory,description,url) values(?,?,?,?,?,?,?,?,?)";
		jdbcTemplate.update(sql, new Object[]{book.getTitle(),book.getAuthor(),book.getPublisher(),book.getPublishing_time(),book.getBook_price(),book.getCategory_id(),book.getInventory(),book.getDescription(),book.getUrl()});
	}
	public ArrayList<BeanBook> getallbook(){
		String sql="select book_id,title from books";
		ArrayList<BeanBook> books=new ArrayList<>();
		this.jdbcTemplate.query(sql, new ResultSetExtractor<BeanBook>() {

			@Override
			public BeanBook extractData(ResultSet rs) throws SQLException, DataAccessException {
				// TODO Auto-generated method stub
				while(rs.next()) {
					BeanBook book=new BeanBook();
					book.setBook_id(rs.getString(1));
					book.setTitle(rs.getString(2));
					books.add(book);
				}
				return null;
			}
			
		});
		return books;
	}
	public void deleteBook(String bookid) {
		String sql="select order_id from order_details where book_id=?";
	
		this.jdbcTemplate.query(sql, new ResultSetExtractor<BeanOrderDetail>() {

			@Override
			public BeanOrderDetail extractData(ResultSet rs) throws SQLException, DataAccessException {
				// TODO Auto-generated method stub
				boolean first=true;
				String orderids="";
				while(rs.next()) {
					if(first==false)orderids+=",";
					else first=false;
					orderids+=rs.getInt(1);
				}
				return null;
			}
			
		});
		sql="delete from order_details where book_id=?";
		jdbcTemplate.update(sql, new Object[]{bookid});
		sql="delete from books where book_id=?";
		sql="delete from books where book_id=?";
		jdbcTemplate.update(sql, new Object[]{bookid});
	}
	public void updateBook(BeanBook book) {
		String sql="update books set title=?,author=?,publisher=?,publishing_time=?,book_price=?,category_id=?,inventory=?,description=?,url=? where book_id=?";
		jdbcTemplate.update(sql, new Object[]{book.getTitle(),book.getAuthor(),book.getPublisher(),book.getPublishing_time(),book.getBook_price(),book.getCategory_id(),book.getInventory(),book.getDescription(),book.getBook_id(),book.getUrl()});
	}
	public int getNumberOfBooks() throws BooksNotFoundException {
		books = new ArrayList();
		String selectStatement = "select * " + "from books";
		this.jdbcTemplate.query(selectStatement, new ResultSetExtractor<BeanBook>() {
			@Override
			public BeanBook extractData(ResultSet rs) throws SQLException, DataAccessException {
				// TODO Auto-generated method stub
				while (rs.next()) {
					BeanBook bd = new BeanBook();
					bd.setBook_id(rs.getString(1)); 
					bd.setTitle(rs.getString(2));
					bd.setAuthor(rs.getString(3));
					bd.setPublisher(rs.getString(4));
					bd.setPublishing_time(rs.getInt(5));
					bd.setBook_price(rs.getFloat(6));
					bd.setCategory_id(rs.getInt(7));
					bd.setInventory(rs.getInt(8));
					bd.setDescription(rs.getString(9));
					if (rs.getInt(8) > 0)
						books.add(bd);
				}
				return null;
			}
		});
		return books.size();
	}
	public ArrayList<String> gettop() throws BooksNotFoundException{
		String selectStatement = "select bookid,avg(score),COUNT(bookid) from rating GROUP BY(bookid) ORDER BY avg(score) DESC,count(bookid) DESC ";
		ArrayList<String> bookids = new ArrayList<>();
		this.jdbcTemplate.query(selectStatement, new ResultSetExtractor<BeanRating>() {
			@Override
			public BeanRating extractData(ResultSet rs) throws SQLException, DataAccessException {
				// TODO Auto-generated method stub
				int i=0;
				while (rs.next()) {
					i++;
					bookids.add(rs.getString(1));
					if(i>=10)break;			
				}
				return null;
			}
		});
		return bookids;
	}
	public Collection<BeanBook> getAllBooks(String keyword) throws BooksNotFoundException {
		books = new ArrayList();
		String selectStatement;
		if(!keyword.equals("null")) {
			selectStatement = "select * " + "from books where title like '%"+keyword+"%'";
		}
		else selectStatement = "select * from books";
		this.jdbcTemplate.query(selectStatement, new ResultSetExtractor<BeanBook>() {
			@Override
			public BeanBook extractData(ResultSet rs) throws SQLException, DataAccessException {
				// TODO Auto-generated method stub
				while (rs.next()) {
					BeanBook bd = new BeanBook();
					bd.setBook_id(rs.getString(1)); 
					bd.setTitle(rs.getString(2));
					bd.setAuthor(rs.getString(3));
					bd.setPublisher(rs.getString(4));
					bd.setPublishing_time(rs.getInt(5));
					bd.setBook_price(rs.getFloat(6));
					bd.setCategory_id(rs.getInt(7));
					bd.setInventory(rs.getInt(8));
					bd.setDescription(rs.getString(9));
					bd.setUrl(rs.getString(10));
					if (rs.getInt(8) > 0)
						books.add(bd);
				}
				return null;
			}
		});
		return books;
	}
	public float getrating(String bookid) throws Exception {
		String sql="select score from rating where bookid=?";
		double average=this.jdbcTemplate.query(sql, new ResultSetExtractor<Double>(){

			@Override
			public Double extractData(ResultSet rs) throws SQLException, DataAccessException {
				// TODO Auto-generated method stub
				int num=0;
				double sum=0;
				while(rs.next()) {
					num++;
					sum+=rs.getInt(1);
				}
				System.out.println(num);
				System.out.println(sum);
				if(num!=0)
					sum/=num;
				return sum;
			}
			
		},bookid);
		System.out.println(average);
		return (float) average;
	}
	public Collection<BeanBook> getByCategory(int category) throws BooksNotFoundException {
		books = new ArrayList<>();
		String selectStatement = "select * " + "from books "+"where category_id=?";
		this.jdbcTemplate.query(selectStatement, new ResultSetExtractor<BeanBook>() {
			@Override
			public BeanBook extractData(ResultSet rs) throws SQLException, DataAccessException {
				// TODO Auto-generated method stub
				while (rs.next()) {
					BeanBook bd = new BeanBook();
					bd.setBook_id(rs.getString(1)); 
					bd.setTitle(rs.getString(2));
					bd.setAuthor(rs.getString(3));
					bd.setPublisher(rs.getString(4));
					bd.setPublishing_time(rs.getInt(5));
					bd.setBook_price(rs.getFloat(6));
					bd.setCategory_id(rs.getInt(7));
					bd.setInventory(rs.getInt(8));
					bd.setDescription(rs.getString(9));
					bd.setUrl(rs.getString(10));
					if (rs.getInt(8) > 0)
						books.add(bd);
				}
				return null;
			}
		},category);
		//Collections.sort(books);
		return books;
	}	
	public Collection<BeanBook> getBooks(String name) throws BooksNotFoundException {
		books = new ArrayList<>();
		String selectStatement = null;
		if(name=="")selectStatement = "select * " + "from books";
		else selectStatement = "select * " + "from books "+"where title like '%"+name+"%' or author like '%"+name+"%' or description like '%"+name+"%'";
		this.jdbcTemplate.query(selectStatement, new ResultSetExtractor<BeanBook>() {
			@Override
			public BeanBook extractData(ResultSet rs) throws SQLException, DataAccessException {
				// TODO Auto-generated method stub
				while (rs.next()) {
					BeanBook bd = new BeanBook();
					bd.setBook_id(rs.getString(1)); 
					bd.setTitle(rs.getString(2));
					bd.setAuthor(rs.getString(3));
					bd.setPublisher(rs.getString(4));
					bd.setPublishing_time(rs.getInt(5));
					bd.setBook_price(rs.getFloat(6));
					bd.setCategory_id(rs.getInt(7));
					bd.setInventory(rs.getInt(8));
					bd.setDescription(rs.getString(9));
					bd.setUrl(rs.getString(10));
					if (rs.getInt(8) > 0)
						books.add(bd);
				}
				return null;
			}
		});
		//Collections.sort(books);
		return books;
	}
	
	public BeanBook getBookDetails(String bookId) throws BookNotFoundException {
		String selectStatement = "select * " + "from books where book_id = ? ";
		BeanBook bookDetails = this.jdbcTemplate.query(selectStatement,new Object[] {bookId}, new ResultSetExtractor<BeanBook>() {
			@Override
			public BeanBook extractData(ResultSet rs) throws SQLException, DataAccessException {
				// TODO Auto-generated method stub
				if (rs.next()) {
					BeanBook bd = new BeanBook();
					bd.setBook_id(rs.getString(1)); 
					bd.setTitle(rs.getString(2));
					bd.setAuthor(rs.getString(3));
					bd.setPublisher(rs.getString(4));
					bd.setPublishing_time(rs.getInt(5));
					bd.setBook_price(rs.getFloat(6));
					bd.setCategory_id(rs.getInt(7));
					bd.setInventory(rs.getInt(8));
					bd.setDescription(rs.getString(9));
					bd.setUrl(rs.getString(10));
					try {
						bd.setScore(getrating(bd.getBook_id()));
						System.out.println(bd.getScore());
					} catch (Exception e) {
						// TODO Auto-generated catch block
						e.printStackTrace();
					}
					return bd;
				}
				return null;
			}
		});
		if(bookDetails==null)throw new BookNotFoundException("Couldn't find book: " + bookId);
		System.out.println(bookDetails.getTitle());
		return bookDetails;
	}
	public void buyBooks(ShoppingCart cart) throws OrderException {
//		Collection items = cart.getItems();
//		Iterator i = items.iterator();
//		try {
//			while (i.hasNext()) {
//				ShoppingCartItem sci = (ShoppingCartItem) i.next();
//				BeanBook bd = (BeanBook) sci.getItem();
//				String id = bd.getBook_id();
//				int quantity = sci.getQuantity();
//				buyBook(id, quantity);
//			}
//		}
//		catch (Exception ex) {
//			throw new OrderException("Transaction failed: " + ex.getMessage());
//		}
	}
	public void buyBook(String bookId, int quantity) throws OrderException {
		try {
			String selectStatement = "select * " + "from books where book_id = ? ";
			this.jdbcTemplate.query(selectStatement, new ResultSetExtractor<BeanBook>() {
				@Override
				public BeanBook extractData(ResultSet rs) throws SQLException, DataAccessException {
					// TODO Auto-generated method stub
					if (rs.next()) {
						int inventory = rs.getInt(8);
						if ((inventory - quantity) >= 0) {
							String updateStatement = "update books set inventory = inventory - ? where book_id = ?";
							jdbcTemplate.update(updateStatement, new Object[] { quantity, bookId });
						} else {
							try {
								throw new OrderException("Not enough of " + bookId + " in stock to complete order.");
							} catch (OrderException e) {
								// TODO Auto-generated catch block
								e.printStackTrace();
							}
						}	
					}
					return null;
				}
			}, bookId);
		} catch (Exception ex) {
			throw new OrderException("Couldn't purchase book: " + bookId + ex.getMessage());
		}
	}
}
